﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq.SqlClient;

namespace ChurchData.Business
{
    public class PeopleBO : ChurchDataBusinessBase
    {
        #region Get Methods
        public List<PeopleSearchResults> LookupPeople(string firstName, string lastName, string email, string phone)
        {
            ChurchDataDataContext oDB = new ChurchDataDataContext(this.GetConnectionString());

            IEnumerable<PeopleSearchResults> x =
                from p in oDB.Peoples
                join pc1 in oDB.PeopleContacts on p.PeoplePK equals pc1.PeopleFK into sr
                from pc in sr.DefaultIfEmpty()
                where
                SqlMethods.Like(p.FirstName, firstName + "%")
                && SqlMethods.Like(p.LastName, lastName + "%")
                && p.ActiveInd == true
                && (email == "" && phone == "" || pc.PeopleContactPK != null)
                && (pc.PeopleContactPK == null || pc.PeopleContactPK < 1 ||
                       (
                           (email == "" ||
                              (pc.ActiveInd == true &&
                                   (SqlMethods.Like(pc.PersonalEmail, "%" + email + "%")
                                    || SqlMethods.Like(pc.FamilyEmail, "%" + email + "%"))
                              )
                           )
                           && (phone == "" ||
                                   (pc.ActiveInd == true &&
                                       (SqlMethods.Like(pc.HomePhone, "%" + phone + "%")
                                        || SqlMethods.Like(pc.WorkPhone, "%" + phone + "%")
                                        || SqlMethods.Like(pc.CellPhone, "%" + phone + "%"))
                                   )
                              )
                      )
                   )
                select new PeopleSearchResults { PeoplePK = p.PeoplePK, FirstName = p.FirstName, LastName = p.LastName };

            return x.ToList();
        }

        public List<PeopleSearchResults> LookupPeopleNotRelatedYet(int peoplePK, string firstName, string lastName)
        {
            ChurchDataDataContext oDB = new ChurchDataDataContext(this.GetConnectionString());

            IEnumerable<PeopleSearchResults> x =
                from p in oDB.Peoples
                where p.PeoplePK != peoplePK
                && SqlMethods.Like(p.FirstName, firstName + "%")
                && SqlMethods.Like(p.LastName, lastName + "%")
                && p.ActiveInd == true
                select new PeopleSearchResults { PeoplePK = p.PeoplePK, FirstName = p.FirstName, LastName = p.LastName };

            // Filter out people already related to them.
            PeopleRelatedBO prBO = new PeopleRelatedBO();
            List<PeopleRelationshipDisplay> relationships = prBO.GetListForDisplay(peoplePK);

            List<PeopleSearchResults> results = new List<PeopleSearchResults>();

            foreach (PeopleSearchResults rs in x)
            {
                int cnt = (from rec in relationships
                           where rec.PeoplePK == rs.PeoplePK
                           select rec).Count();

                if (cnt < 1) results.Add(rs);
            }

            return results;
        }

        public List<PeopleSearchResults> LookupPeopleNotInHouseholdYet(int peoplePK, string firstName, string lastName)
        {
            ChurchDataDataContext oDB = new ChurchDataDataContext(this.GetConnectionString());

            IEnumerable<PeopleSearchResults> x =
                from p in oDB.Peoples
                where p.PeoplePK != peoplePK
                && SqlMethods.Like(p.FirstName, firstName + "%")
                && SqlMethods.Like(p.LastName, lastName + "%")
                && p.HouseholdFK == null
                && p.ActiveInd == true
                select new PeopleSearchResults { PeoplePK = p.PeoplePK, FirstName = p.FirstName, LastName = p.LastName };

            return x.ToList();
        }

        public List<PeopleSearchResults> LookupHeadsOfHousehold(string firstName, string lastName)
        {
            ChurchDataDataContext oDB = new ChurchDataDataContext(this.GetConnectionString());

            IEnumerable<PeopleSearchResults> x =
                from p in oDB.Peoples
                where SqlMethods.Like(p.FirstName, firstName + "%")
                && SqlMethods.Like(p.LastName, lastName + "%")
                && p.HouseholdFK != null
                && p.ActiveInd == true
                && p.HeadOfHouseInd == true
                select new PeopleSearchResults { PeoplePK = p.PeoplePK, FirstName = p.FirstName, LastName = p.LastName };

            return x.ToList();
        }

        public People GetRecordByPK(int peoplePK)
        {
            ChurchDataDataContext oDB = new ChurchDataDataContext(this.GetConnectionString());

            IEnumerable<People> results = from p in oDB.Peoples
                                            where p.PeoplePK == peoplePK
                                            select p;

            return results.First();
        }

        public People GetHeadOfHouseByPeoplePK(int peoplePK)
        {
            int? householdFK = this.GetRecordByPK(peoplePK).HouseholdFK;
            if (householdFK == null) return null;

            ChurchDataDataContext oDB = new ChurchDataDataContext(this.GetConnectionString());

            IEnumerable<People> result = from p in oDB.Peoples
                         where p.HouseholdFK == householdFK
                         && p.HeadOfHouseInd == true
                         select p;

            if (result.Count() == 0)
                return null;
            else
                return result.First();
        }
        #endregion Get Methods

        #region Add Methods
        public int AddPerson(string firstName, string lastName)
        {
            ChurchDataDataContext oDB = new ChurchDataDataContext(this.GetConnectionString());
            People person = new People();
            oDB.Peoples.InsertOnSubmit(person);

            person.FirstName = firstName;
            person.LastName = lastName;
            person.ActiveInd = true;
            person.LastUpdTimestamp = DateTime.Now;
            person.LastUpdUser = UserInformation.UserName;

            oDB.SubmitChanges();

            return person.PeoplePK;
        }

        #endregion Add Methods

        #region Update Methods
        public void UpdateBasic(int peoplePK, string firstName, string lastName, string suffix, string nickName, string title, 
            string gender, DateTime? birthDate, int? maritalStatus, int? association, string notes, string specialNeeds,
            System.Data.Linq.Binary img)
        {
            ChurchDataDataContext oDB = new ChurchDataDataContext(this.GetConnectionString());
            People person = (from p in oDB.Peoples
                              where p.PeoplePK == peoplePK
                              select p).First();

            person.FirstName = firstName;
            person.LastName = lastName;
            person.Suffix = suffix;
            person.Nickname = nickName;
            person.Title = title;
            person.Gender = Convert.ToChar(gender);
            person.Birthdate = birthDate;
            person.LKMaritalStatusFK = maritalStatus;
            person.LKAssociationFK = association;
            person.Notes = notes;
            person.SpecialNeeds = specialNeeds;
            person.Picture = img;
            person.LastUpdTimestamp = DateTime.Now;
            person.LastUpdUser = UserInformation.UserName;

            oDB.SubmitChanges();
        }

        public void UpdateSecureNotes(int peoplePK, string secureNotes)
        {
            ChurchDataDataContext oDB = new ChurchDataDataContext(this.GetConnectionString());
            People person = (from p in oDB.Peoples
                             where p.PeoplePK == peoplePK
                             select p).First();

            person.SecureNotes = secureNotes;
            person.LastUpdTimestamp = DateTime.Now;
            person.LastUpdUser = UserInformation.UserName;

            oDB.SubmitChanges();
        }

        public void DeletePerson(int peoplePK)
        {
            ChurchDataDataContext oDB = new ChurchDataDataContext(this.GetConnectionString());

            People drP = (from p in oDB.Peoples
                          where p.PeoplePK == peoplePK
                          select p).First();

            drP.ActiveInd = false;
            oDB.SubmitChanges();
        }

        public void UpdateHousehold(int peoplePK, int? householdFK)
        {
            ChurchDataDataContext oDB = new ChurchDataDataContext(this.GetConnectionString());

            People drP = (from p in oDB.Peoples
                          where p.PeoplePK == peoplePK
                          select p).First();

            drP.HouseholdFK = householdFK;
            drP.HeadOfHouseInd = false;

            oDB.SubmitChanges();
        }
        #endregion Update Methods
    }

    #region Return Classes
    public class PeopleSearchResults
    {
        public int PeoplePK { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    #endregion Return Classes
}